import csv
from openpyxl import load_workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.styles import PatternFill, Alignment, Font, colors
import xlwings as xw
from PIL import ImageGrab
import operator



# 根据类别筛选数目
def category_num(csv_path, category):
    with open(csv_path, mode="r", encoding="GBK") as f:
        csvdata = csv.DictReader(f)
        all_data = []
        new_data = []
        num_list = []
        datas = []
        for data in csvdata:
            categ = data[category]
            all_data.append(categ)
        for x in all_data:
            if x not in new_data:
                new_data.append(x)   # 得到去重后的类别
        for data in new_data:
            num = all_data.count(data)
            num_list.append(num)   # 去重后的类别中，每个元素的数量
        for j in range(len(num_list)):
            dict = {}
            dict["name"] = new_data[j]
            dict["num"] = num_list[j]
            datas.append(dict)
        return datas

# 根据类别筛选bug的数目
def bug_num(csv_path, category):
    with open(csv_path, mode="r", encoding="GBK") as f:
        csvdata = csv.DictReader(f)
        all_data = []
        new_data = []
        num_list = []
        datas = []
        for data in csvdata:
            if data['跟踪'] == 'Bug':
                categ = data[category]
                all_data.append(categ)
        for x in all_data:
            if x not in new_data:
                new_data.append(x)   # 得到去重后的类别
        for data in new_data:
            num = all_data.count(data)
            num_list.append(num)   # 去重后的类别中，每个元素的数量
        for j in range(len(num_list)):
            dict = {}
            dict["name"] = new_data[j]
            dict["num"] = num_list[j]
            datas.append(dict)
        return datas

# 将筛选结果写入excel
def write_excel(csv_path, result_path):
    wb = load_workbook(filename=result_path)
    ws: Worksheet = wb.active
    # 写入根据跟踪统计的数目
    ws['A1'] = "1、测试内容"
    ws['A2'] = "跟踪"
    ws['B2'] = "数目"
    genzong_datas = category_num(csv_path, "跟踪")
    genzong_row = 2
    for gengzongdata in genzong_datas:
        genzong_row += 1
        ws.cell(row=genzong_row, column=1, value=gengzongdata['name'])
        ws.cell(row=genzong_row, column=2, value=gengzongdata['num'])

    # 写入根据优先级统计的bug数目
    ws['A8'] = "2、各级别bug统计"
    ws['A9'] = "严重等级"
    ws['B9'] = "数目"
    level_datas = bug_num(csv_path, "优先级")
    level_row = 9
    for leveldata in level_datas:
        level_row += 1
        ws.cell(row=level_row, column=1, value=leveldata['name'])
        ws.cell(row=level_row, column=2, value=leveldata['num'])

    # 写入根据模块统计的bug数目
    ws['D1'] = "3、bug总数：{}".format(ws.cell(3, 2).value)
    ws['D2'] = "模块"
    ws['E2'] = "BUG数"
    ws['F2'] = "模块"
    ws['G2'] = "BUG数"
    module_datas = bug_num(csv_path, "类别")
    # 降序处理
    module_datas.sort(key=operator.itemgetter('num'), reverse=True)
    module_row = 2
    for moduledata in module_datas:
        module_row += 1
        if module_row <= 25:
            ws.cell(row=module_row, column=4, value=moduledata['name'])
            ws.cell(row=module_row, column=5, value=moduledata['num'])
        else:
            module_row2 = module_row-23
            ws.cell(row=module_row2, column=6, value=moduledata['name'])
            ws.cell(row=module_row2, column=7, value=moduledata['num'])

    # 设置样式
    ws.merge_cells('A1:B1')
    ws.merge_cells('A8:B8')
    ws.merge_cells('D1:G1')
    ws.column_dimensions['D'].width = 14
    ws.column_dimensions['F'].width = 14
    for fill_cell in [ws['A1'], ws['A8'], ws['D1']]:
        fill_cell.font = Font(bold=True, color=colors.RED)
        fill_cell.fill = PatternFill("solid", fgColor=colors.YELLOW)
    for bold_cell in [ws['A2'], ws['B2'], ws['A9'], ws['B9'], ws['D2'], ws['E2'], ws['F2'], ws['G2']]:
        bold_cell.font = Font(bold=True)
    for row in ws.iter_rows(min_row=1, max_row=30, min_col=1, max_col=10, values_only=False):
        for eachCellInRow in row:
            eachCellInRow.alignment = Alignment(horizontal='center', vertical='center')

    wb.save(result_path)


# 在excel中指定区域保存图片
def excel_save_img(result_path, img_name, img_suffix="png"):
    app = xw.App(visible=True, add_book=False)
    # 1. 使用 xlwings 的 读取 path 文件 启动
    wb = app.books.open(result_path)
    # 2. 读取 sheet,数据都存在第一个sheet页，所以只要读取第一页
    sht = wb.sheets[0]
    # 4. 获取有指定区域的 range
    for range_val in [sht.range((1, 1), (13, 2)), sht.range((1, 4), (26, 7))]:
        # 5. 复制图片区域
        range_val.api.CopyPicture()
        # 6. 粘贴
        sht.api.Paste()
        pic = sht.pictures[0]  # 当前图片
        pic.api.Copy()  # 复制图片
        img = ImageGrab.grabclipboard()  # 获取剪贴板的图片数据
        img.save(img_name + "." + img_suffix)  # 保存图片
        img_name = img_name + '1'
        pic.delete()  # 删除sheet上的图片
    wb.close()  # 不保存，直接关闭
    app.quit()  # 退出

def get_result(csv_path, result_path,img_name):
    write_excel(csv_path, result_path)
    excel_save_img(result_path, img_name)


if __name__ == '__main__':
    get_result('./issues.csv', './result.xlsx', '700')












